package com.grades.springboot.mapper;


import com.grades.springboot.entity.Admin;
import com.grades.springboot.entity.Result;
import com.grades.springboot.entity.Teacher;
import com.grades.springboot.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface TeacherMapper {

    @Select("select *from teacher where id=#{id}")
    Teacher selectById(Integer id);

    @Select("select *from teacher where email=#{email}")
    Teacher selectByEmail(String email);

    @Update("update teacher set password=#{password} where email=#{email}")
    int updatePassword(String email, String password);

    @Select("select id,email,phone_number,id_card,name,gender,age,subjects.name as subject " +
            "from teacher left join subjects on teacher.subject=subject.id order by id")
    List<Teacher> getTeacherList();

    @Select("select t.id,t.email,t.phone_number,t.id_card,t.name,t.gender,t.age,s.name as 'subject' " +
            "from teacher as t left join subjects as s " +
            "on t.subject = s.id where t.id " +
            "in (select teacher_id from class_teacher where class_id = #{classId})")
    List<Teacher> getTeacherByClass(Integer classId);


    @Select("select t.id,t.email,t.phone_number,t.id_card,t.name,t.gender,t.age from teacher as t " +
            "where t.subject = (select id from subjects where name = #{subjectName})")
    List<Teacher> getTeacherBySubject(String subjectName);

    @Select("select id,email,phone_number,id_card,name,gender,age,subjects.name as subject " +
            "from teacher left join subjects on teacher.subject=subject.id where teacher.id=#{id}")
    Teacher getTeacherById(Integer id);

    @Select("select id,email,phone_number,id_card,name,gender,age,subjects.name as subject " +
            "from teacher left join subjects on teacher.subject=subject.id where teacher.name=#{name}")
    List<Teacher> getTeacherByName(String name);

    @Insert("insert into teacher values(#{password},#{email},#{phoneNumber},#{idCard},#{name},#{gender},#{age},(select id from subjects where name=#{subject}))")
    int addTeacher(Teacher teacher);
}
